with f as (
  select
        id                                
       ,work_order_no                     
       ,waybill_no                        
       ,order_no                          
       ,work_order_channel                
       ,work_order_status                 
       ,finish_status                     
       ,first_problem_type                
       ,work_order_source_id              
       ,send_back_code                    
       ,send_back_count                   
       ,problem_desc                      
       ,remarks                           
       ,file_json                         
       ,initiator_name                    
       ,initiator_mobile                  
       ,accept_network_id                 
       ,accept_network_name               
       ,accept_code                       
       ,accept_name                       
       ,accept_agency_network_id          
       ,accept_agency_network_name        
       ,emergency_level                   
       ,operator_id                       
       ,operator_name                     
       ,responsibility_network_id         
       ,responsibility_network_name       
       ,sham_signed_flag                  
       ,pdd_created_at                    
       ,pdd_updated_at                    
       ,create_time                       
       ,update_time                       
       ,return_time                       
       ,last_close_time                   
       ,last_reply                        
       ,last_duration_time                
       ,overtime_finish                   
       ,auto_finish                       
       ,receive_address                   
       ,pay_amount                        
       ,receiver_id                       
       ,`sync`                            
       ,reject_count                      
       ,last_reject_time                  
       ,last_approval_time                
       ,last_approval_user_id             
       ,last_approval_name                
       ,approval_status                   
       ,approval_comment                  
       ,upgrade_status                    
       ,response_status                   
       ,partner_name                      
       ,express_cabinet                   
       ,send_address                      
       ,first_level_code                  
       ,first_level_name                  
       ,second_level_code                 
       ,second_level_name                 
       ,res_franchisee_network_id         
       ,res_franchisee_network_name       
       ,res_agency_network_id             
       ,res_agency_network_name           
       ,close_network_id                  
       ,close_network_name                
       ,accept_network_type               
       ,response_duration                 
       ,monitoring_time                   
       ,process_duration                  
       ,is_monitor_history                
       ,service_id                        
       ,project_work_company              
       ,ticket_expire_time                
       ,ticket_status                     
       ,ticket_first_type                 
       ,ticket_second_type                
       ,second_complaint                  
       ,handle_times                      
       ,product_info                      
       ,callback_contact                  
       ,creator_role                      
       ,receiver_name                     
       ,receiver_contact                  
       ,incoming_time                     
       ,accept_franchisee_id              
       ,accept_franchisee_name
       ,sub_bill_code
        ,staff_code
        ,staff_name
       ,row_number() over(partition by waybill_no order by update_time desc) as rank
  from jms_ods.project_work_order
    where dt between date_sub('{{ execution_date | cst_ds }}',60)
         and date_add('{{ execution_date | cst_ds }}',60)
             and date(create_time) between date_sub('{{ execution_date | cst_ds }}',60)
             and '{{ execution_date | cst_ds }}'
)
insert overwrite table jms_dwd.dwd_project_work_order_dt partition(dt)
  select
        id                                
       ,work_order_no                     
       ,waybill_no                        
       ,order_no                          
       ,work_order_channel                
       ,work_order_status                 
       ,finish_status                     
       ,first_problem_type                
       ,work_order_source_id              
       ,send_back_code                    
       ,send_back_count                   
       ,problem_desc                      
       ,remarks                           
       ,file_json                         
       ,initiator_name                    
       ,initiator_mobile                  
       ,accept_network_id                 
       ,accept_network_name               
       ,accept_code                       
       ,accept_name                       
       ,accept_agency_network_id          
       ,accept_agency_network_name        
       ,emergency_level                   
       ,operator_id                       
       ,operator_name                     
       ,responsibility_network_id         
       ,responsibility_network_name       
       ,sham_signed_flag                  
       ,pdd_created_at                    
       ,pdd_updated_at                    
       ,create_time                       
       ,update_time                       
       ,return_time                       
       ,last_close_time                   
       ,last_reply                        
       ,last_duration_time                
       ,overtime_finish                   
       ,auto_finish                       
       ,receive_address                   
       ,pay_amount                        
       ,receiver_id                       
       ,`sync`                            
       ,reject_count                      
       ,last_reject_time                  
       ,last_approval_time                
       ,last_approval_user_id             
       ,last_approval_name                
       ,approval_status                   
       ,approval_comment                  
       ,upgrade_status                    
       ,response_status                   
       ,partner_name                      
       ,express_cabinet                   
       ,send_address                      
       ,first_level_code                  
       ,first_level_name                  
       ,second_level_code                 
       ,second_level_name                 
       ,res_franchisee_network_id         
       ,res_franchisee_network_name       
       ,res_agency_network_id             
       ,res_agency_network_name           
       ,close_network_id                  
       ,close_network_name                
       ,accept_network_type               
       ,response_duration                 
       ,monitoring_time                   
       ,process_duration                  
       ,is_monitor_history                
       ,service_id                        
       ,project_work_company              
       ,ticket_expire_time                
       ,ticket_status                     
       ,ticket_first_type                 
       ,ticket_second_type                
       ,second_complaint                  
       ,handle_times                      
       ,product_info                      
       ,callback_contact                  
       ,creator_role                      
       ,receiver_name                     
       ,receiver_contact                  
       ,incoming_time                     
       ,accept_franchisee_id              
       ,accept_franchisee_name
       ,sub_bill_code
       ,staff_code
       ,staff_name
       ,date(create_time) as dt
 from f
   where rank = 1
          distribute by pmod(id,3) ;
